package Tool;

import Javabean.Staff;

import java.sql.*;
import java.util.ArrayList;

public class MySQL_StaffSearch {
    public static void staffIdSearch(Connection conn, ArrayList<Staff> staffs, String id) throws SQLException {
        //定义sql
        //查询
        String sql = "select * from staffsystem.staff where id = " + id;

        //获取执行sql的对象 Stamant
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //执行sql
        ResultSet rs = pstmt.executeQuery(sql);

        //处理结果
        while (rs.next()) {
            Staff staff = new Staff();
            staff.setId(rs.getInt(1));
            staff.setName(rs.getString(2));
            staff.setSex(rs.getString(3));
            staff.setBrithday(rs.getString(4));
            staff.setAge(rs.getInt(5));
            staff.setMarry(rs.getInt(6));
            staff.setPlace(rs.getString(7));
            staff.setStaff(rs.getString(8));
            staff.setLevel(rs.getInt(9));
            staff.setSalary(rs.getDouble(10));
            staffs.add(staff);
        }
        //释放资源
        rs.close();
        pstmt.close();
    }


    public static void staffLevelSearch(Connection conn, ArrayList<Staff> staffs, String level) throws SQLException {
        //定义sql
        //查询
        String sql = "select * from staffsystem.staff where Level = " + level;

        //获取执行sql的对象 Stamant
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //执行sql
        ResultSet rs = pstmt.executeQuery(sql);

        //处理结果
        while (rs.next()) {
            Staff staff = new Staff();
            staff.setId(rs.getInt(1));
            staff.setName(rs.getString(2));
            staff.setSex(rs.getString(3));
            staff.setBrithday(rs.getString(4));
            staff.setAge(rs.getInt(5));
            staff.setMarry(rs.getInt(6));
            staff.setPlace(rs.getString(7));
            staff.setStaff(rs.getString(8));
            staff.setLevel(rs.getInt(9));
            staff.setSalary(rs.getDouble(10));
            staffs.add(staff);
        }
        //释放资源
        rs.close();
        pstmt.close();
    }


    public static void staffPXSearch(Connection conn, ArrayList<Staff> staffs, int num) throws SQLException {
        //定义sql
        //查询
        String sql0 = "select * from staffsystem.staff order by Salary asc";
        String sql1 = "select * from staffsystem.staff order by Salary desc";
        String sql2 = "select * from staffsystem.staff";

        if (num == 0){

            //获取执行sql0的对象 Stamant
            PreparedStatement pstmt = conn.prepareStatement(sql0);

            //执行sql0
            ResultSet rs = pstmt.executeQuery(sql0);

            //处理结果
            while (rs.next()) {
                Staff staff = new Staff();
                staff.setId(rs.getInt(1));
                staff.setName(rs.getString(2));
                staff.setSex(rs.getString(3));
                staff.setBrithday(rs.getString(4));
                staff.setAge(rs.getInt(5));
                staff.setMarry(rs.getInt(6));
                staff.setPlace(rs.getString(7));
                staff.setStaff(rs.getString(8));
                staff.setLevel(rs.getInt(9));
                staff.setSalary(rs.getDouble(10));
                staffs.add(staff);
            }
            //释放资源
            rs.close();
            pstmt.close();
        } else if (num == 1) {

            //获取执行sql1的对象 Stamant
            PreparedStatement pstmt = conn.prepareStatement(sql1);

            //执行sql
            ResultSet rs = pstmt.executeQuery(sql1);

            //处理结果
            while (rs.next()) {
                Staff staff = new Staff();
                staff.setId(rs.getInt(1));
                staff.setName(rs.getString(2));
                staff.setSex(rs.getString(3));
                staff.setBrithday(rs.getString(4));
                staff.setAge(rs.getInt(5));
                staff.setMarry(rs.getInt(6));
                staff.setPlace(rs.getString(7));
                staff.setStaff(rs.getString(8));
                staff.setLevel(rs.getInt(9));
                staff.setSalary(rs.getDouble(10));
                staffs.add(staff);
            }
            //释放资源
            rs.close();
            pstmt.close();
        }else if (num == 2) {

            //获取执行sql2的对象 Stamant
            PreparedStatement pstmt = conn.prepareStatement(sql2);

            //执行sql2
            ResultSet rs = pstmt.executeQuery(sql2);

            //处理结果
            while (rs.next()) {
                Staff staff = new Staff();
                staff.setId(rs.getInt(1));
                staff.setName(rs.getString(2));
                staff.setSex(rs.getString(3));
                staff.setBrithday(rs.getString(4));
                staff.setAge(rs.getInt(5));
                staff.setMarry(rs.getInt(6));
                staff.setPlace(rs.getString(7));
                staff.setStaff(rs.getString(8));
                staff.setLevel(rs.getInt(9));
                staff.setSalary(rs.getDouble(10));
                staffs.add(staff);
            }
            //释放资源
            rs.close();
            pstmt.close();
        }

    }
}

